package com.exportservices.DAO;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;

import com.exportservices.domain.Client;
import com.exportservices.domain.Color;
import com.exportservices.domain.Curs;
import com.exportservices.domain.Discount;
import com.exportservices.domain.FinDoc;
import com.exportservices.domain.GroupID;
import com.exportservices.domain.GroupSection;
import com.exportservices.domain.Kart;
import com.exportservices.domain.Manager;
import com.exportservices.domain.Price;
import com.exportservices.domain.Rules;
import com.exportservices.domain.Sklad;
import com.exportservices.domain.Tovar;
import com.exportservices.domain.TypeDoc;
import com.exportservices.domain.TypePrice;
import com.exportservices.domain.Val;

public class MainDataBaseDAO {
	
	@Autowired
	JdbcTemplate template;
	
	public Object getListClientWithChange(){
		return template.query("select id_client, name, adres, phone, type, to_char(day,'DD.MM.YYYY HH24:MI'), state from client where not state is null", new Object[] {}, new ResultSetExtractor<Object>(){

			public Object extractData(ResultSet rs) throws SQLException,
					DataAccessException {
				List<Client> clients=new ArrayList<Client>();
				while (rs.next()){
					clients.add(new Client(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getInt(5),rs.getString(6),rs.getInt(7)));
				}
				return clients;
			}
			
		});
	}
	
	public Object getListColorWithChange(){
		return template.query("select id_color, name, short_name, state from color where not state is null", new Object[] {}, new ResultSetExtractor<Object>(){

			public Object extractData(ResultSet rs) throws SQLException,
					DataAccessException {
				List<Color> colors=new ArrayList<Color>();
				while (rs.next()){
					colors.add(new Color(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getInt(4)));
				}
				return colors;
			}
			
		});
	}

	public Object getListCursWithChange(){
		return template.query("select id_val, curs, to_char(day,'DD.MM.YYYY HH24:MI'), state from curs where not state is null", new Object[] {}, new ResultSetExtractor<Object>(){

			public Object extractData(ResultSet rs) throws SQLException,
					DataAccessException {
				List<Curs> curses=new ArrayList<Curs>();
				while (rs.next()){
					curses.add(new Curs(rs.getInt(1),rs.getDouble(2),rs.getString(3),rs.getInt(4)));
				}
				return curses;
			}
			
		});
	}
	
	public Object getListDiscountWithChange(){
		return template.query("select id_client, id_skl, disc, state from discount where not state is null", new Object[] {}, new ResultSetExtractor<Object>(){

			public Object extractData(ResultSet rs) throws SQLException,
					DataAccessException {
				List<Discount> discounts=new ArrayList<Discount>();
				while (rs.next()){
					discounts.add(new Discount(rs.getInt(1),rs.getInt(2),rs.getInt(3),rs.getInt(4)));
				}
				return discounts;
			}
			
		});
	}

	public Object getListFinDocWithChange(){
		return template.query("select id_type_Doc, id_client, id_manager, id_val, numb, to_char(day,'DD.MM.YYYY HH24:MI'), sum, note, id_doc from fin_doc where not numb is null", new Object[] {}, new ResultSetExtractor<Object>(){

			public Object extractData(ResultSet rs) throws SQLException,
					DataAccessException {
				List<FinDoc> finDocs=new ArrayList<FinDoc>();
				while (rs.next()){
					finDocs.add(new FinDoc(rs.getInt(1),rs.getInt(2),rs.getInt(3),rs.getInt(4),rs.getInt(5),rs.getString(6),rs.getDouble(7),rs.getString(8),rs.getInt(9)));
				}
				return finDocs;
			}
			
		});
	}
	
	public Object getListGroupIDWithChange(){
		return template.query("select id_group, name, parent_group, id_section, ban, state from groupid where not state is null", new Object[] {}, new ResultSetExtractor<Object>(){

			public Object extractData(ResultSet rs) throws SQLException,
					DataAccessException {
				List<GroupID> groupIDs=new ArrayList<GroupID>();
				while (rs.next()){
					groupIDs.add(new GroupID(rs.getInt(1),rs.getString(2),rs.getInt(3),rs.getInt(4),rs.getInt(5),rs.getInt(6)));
				}
				return groupIDs;
			}
			
		});
	}
	
	public Object getListGroupSectionWithChange(){
		return template.query("select id_section, description, state from groupsection where not state is null", new Object[] {}, new ResultSetExtractor<Object>(){

			public Object extractData(ResultSet rs) throws SQLException,
					DataAccessException {
				List<GroupSection> groupSections=new ArrayList<GroupSection>();
				while (rs.next()){
					groupSections.add(new GroupSection(rs.getInt(1),rs.getString(2),rs.getInt(3)));
				}
				return groupSections;
			}
			
		});
	}

	public Object getListKartWithChange(){
		return template.query("select id_tovar, id_skl, id_group, id_nom, cost, to_char(day,'DD.MM.YYYY HH24:MI'), val, state from kart where not state is null", new Object[] {}, new ResultSetExtractor<Object>(){

			public Object extractData(ResultSet rs) throws SQLException,
					DataAccessException {
				List<Kart> karts=new ArrayList<Kart>();
				while (rs.next()){
					karts.add(new Kart(rs.getInt(1),rs.getInt(2),rs.getInt(3),rs.getInt(4),rs.getDouble(5),rs.getString(6),rs.getInt(7),rs.getInt(8)));
				}
				return karts;
			}
			
		});
	}

	public Object getListManagerWithChange(){
		return template.query("select id_manager, id_rules, name, password, to_char(day,'DD.MM.YYYY HH24:MI'), complete, text, state from Manager where not state is null", new Object[] {}, new ResultSetExtractor<Object>(){

			public Object extractData(ResultSet rs) throws SQLException,
					DataAccessException {
				List<Manager> managers=new ArrayList<Manager>();
				while (rs.next()){
					managers.add(new Manager(rs.getInt(1),rs.getInt(2),rs.getString(3),rs.getString(4),rs.getString(5),rs.getInt(6)==1,rs.getString(7),rs.getInt(8)));
				}
				return managers;
			}
			
		});
	}
	
	public Object getListPriceWithChange(){
		return template.query("select id_tovar, id_skl, id_price, cost, akciya, isakcia, state from Price where not state is null", new Object[] {}, new ResultSetExtractor<Object>(){

			public Object extractData(ResultSet rs) throws SQLException,
					DataAccessException {
				List<Price> prices=new ArrayList<Price>();
				while (rs.next()){
					prices.add(new Price(rs.getInt(1),rs.getInt(2),rs.getInt(3),rs.getDouble(4),rs.getInt(5),rs.getInt(6)==1,rs.getInt(7)));
				}
				return prices;
			}
			
		});
	}
	
	public Object getListRulesWithChange(){
		return template.query("select id_rules, name, id_doc, state from Rules where not state is null", new Object[] {}, new ResultSetExtractor<Object>(){

			public Object extractData(ResultSet rs) throws SQLException,
					DataAccessException {
				List<Rules> rules=new ArrayList<Rules>();
				while (rs.next()){
					rules.add(new Rules(rs.getInt(1),rs.getString(2),rs.getString(3),rs.getInt(4)));
				}
				return rules;
			}
			
		});
	}
	
	public Object getListSkladWithChange(){
		return template.query("select id_skl, name, id_price, state from Sklad where not state is null", new Object[] {}, new ResultSetExtractor<Object>(){

			public Object extractData(ResultSet rs) throws SQLException,
					DataAccessException {
				List<Sklad> sklads=new ArrayList<Sklad>();
				while (rs.next()){
					sklads.add(new Sklad(rs.getInt(1),rs.getString(2),rs.getInt(3),rs.getInt(4)));
				}
				return sklads;
			}
			
		});
	}
	
	public Object getListTovarWithChange(){
		return template.query("select id_tovar, name, kol, id_color, state from Tovar where not state is null", new Object[] {}, new ResultSetExtractor<Object>(){

			public Object extractData(ResultSet rs) throws SQLException,
					DataAccessException {
				List<Tovar> tovars=new ArrayList<Tovar>();
				while (rs.next()){
					tovars.add(new Tovar(rs.getInt(1),rs.getString(2),rs.getInt(3),rs.getInt(4),rs.getInt(5)));
				}
				return tovars;
			}
			
		});
	}

	public Object getListTypeDocWithChange(){
		return template.query("select id_type_doc, name, operacia, state from type_doc where not state is null", new Object[] {}, new ResultSetExtractor<Object>(){

			public Object extractData(ResultSet rs) throws SQLException,
					DataAccessException {
				List<TypeDoc> typeDocs=new ArrayList<TypeDoc>();
				while (rs.next()){
					typeDocs.add(new TypeDoc(rs.getInt(1),rs.getString(2),rs.getInt(3),rs.getInt(4)));
				}
				return typeDocs;
			}
			
		});
	}
	
	public Object getListTypePriceWithChange(){
		return template.query("select id_price, name, id_val, state from type_price where not state is null", new Object[] {}, new ResultSetExtractor<Object>(){

			public Object extractData(ResultSet rs) throws SQLException,
					DataAccessException {
				List<TypePrice> typePrices=new ArrayList<TypePrice>();
				while (rs.next()){
					typePrices.add(new TypePrice(rs.getInt(1),rs.getString(2),rs.getInt(3),rs.getInt(4)));
				}
				return typePrices;
			}
			
		});
	}
	
	public Object getListValWithChange(){
		return template.query("select id_val, name, state from val where not state is null", new Object[] {}, new ResultSetExtractor<Object>(){

			public Object extractData(ResultSet rs) throws SQLException,
					DataAccessException {
				List<Val> vals=new ArrayList<Val>();
				while (rs.next()){
					vals.add(new Val(rs.getInt(1),rs.getString(2),rs.getInt(3)));
				}
				return vals;
			}
			
		});
	}


}
